package com.fantuan.db.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Service;

import com.fantuan.db.bean.UserMoney;

@Service
public class UserMoneyDAO extends BaseDAO{

	public UserMoney getUserMoney(int nid) throws SQLException {

		Connection conn = dataSource.getConnection();

		String sql = "select * from t_yw_ftusermoney where `nID`=?";

		UserMoney result = null;
		try {

			Object[] objs = new Object[] { nid };

			result = (UserMoney) this.query(conn, sql, new BeanHandler<UserMoney>(
					UserMoney.class), objs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.close(conn);
		}

		return result;
	}

	
	
	public List<UserMoney> getUserMoneyListByGroupID(int groupid) throws SQLException {

		Connection conn = dataSource.getConnection();

		String sql = "select b.* from t_usercenter a,t_yw_ftusermoney b where a.nid=b.nUserID and b.nGroupID=? order by b.nUserID";

		List<UserMoney> result = null;
		try {

			Object[] objs = new Object[] { groupid };

			result = (List<UserMoney>) this.query(conn, sql, new BeanListHandler<UserMoney>(
					UserMoney.class), objs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.close(conn);
		}

		return result;
	}
	
	
	public List<UserMoney> getUserMoneyListByUserID(int userid) throws SQLException {

		Connection conn = dataSource.getConnection();

		String sql = "select b.* from t_usercenter a,t_yw_ftusermoney b where a.nid=b.nUserID and b.nUserID=? order by b.dUpdateDate desc";

		List<UserMoney> result = null;
		try {

			Object[] objs = new Object[] { userid };

			result = (List<UserMoney>) this.query(conn, sql, new BeanListHandler<UserMoney>(
					UserMoney.class), objs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.close(conn);
		}

		return result;
	}
	
	
	public UserMoney getUserMoney(int nGroupID,int nUserID) throws SQLException {

		Connection conn = dataSource.getConnection();

		String sql = "select * from t_yw_ftusermoney where `nGroupID`=? and `nUserID`=?";

		UserMoney result = null;
		try {

			Object[] objs = new Object[] { nGroupID,nUserID };

			result = (UserMoney) this.query(conn, sql, new BeanHandler<UserMoney>(
					UserMoney.class), objs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.close(conn);
		}

		return result;
	}
	
	
	
	public long addUserMoney(UserMoney userMoney) throws SQLException {

		String sql = "insert into t_yw_ftusermoney(`nGroupID`,`nUserID`,`dBalanceMoney`,`dUpdateDate`,`nConsumeTimes`,`nState`) values(?,?,?,now(),?,?)";

		long id = -1;
		Connection conn = null;
		try {

			conn = dataSource.getConnection();

			Object[] objs = new Object[] { userMoney.getnGroupID(),
					userMoney.getnUserID(), userMoney.getdBalanceMoney(),
					userMoney.getnConsumeTimes(), userMoney.getnState() };
			this.update(conn, sql, objs);

			id = this.getLastInsertID(conn);

			DbUtils.close(conn);
		} catch (SQLException e) {
			DbUtils.close(conn);
			e.printStackTrace();
		}

		return id;
	}
	
	
	
	public void updateUserMoney(UserMoney userMoney) throws SQLException {

		String sql = "update t_yw_ftusermoney set `dBalanceMoney`=?," +
				"`dUpdateDate`=now(),`nConsumeTimes`=?,`nState`=? " +
				"where `nID`=?";

		Connection conn = null;
		try {

			conn = dataSource.getConnection();

			Object[] objs = new Object[] { 
					userMoney.getdBalanceMoney(),
					userMoney.getnConsumeTimes(),
					userMoney.getnState(),
					userMoney.getnID()};
			this.update(conn, sql, objs);

			DbUtils.close(conn);
		} catch (SQLException e) {
			DbUtils.close(conn);
			e.printStackTrace();
		}
	}
	
	
	
	public void updateUserMoney(int groupid, int userid) throws SQLException {

		String sql = "update t_yw_ftusermoney a,"
				+ "("
				+ "SELECT count( distinct(`nConsumeLogID`) ) as nConsumeTimes, sum( `nMoney` ) money,nUserID,nGroupID"
				+ " FROM  `t_yw_ftfundlog` "
				+ " group by nUserID,nGroupID"
				+ ") b"
				+ " set a.dBalanceMoney = b.money, a.nConsumeTimes = b.nConsumeTimes, a.dUpdateDate=now()"
				+ " where a.nUserID=b.nUserID and a.nGroupID=b.nGroupID and a.nUserID=? and a.nGroupID=?";

		Connection conn = null;
		try {

			conn = dataSource.getConnection();

			Object[] objs = new Object[] { 
					userid,groupid
					};
			this.update(conn, sql, objs);

			DbUtils.close(conn);
		} catch (SQLException e) {
			DbUtils.close(conn);
			e.printStackTrace();
		}
	}
}
